【Alteryx】Excelのシリアル値を日付型に変換するには

ExcelをAlteryxで読み込んだ際シリアル値になってしまった場合の対処法のご紹介です！

「カスタマーストーリー」を支えるツール特集

#ビジネス・アナリティクス

#Alteryx

matsuoka

2020.05.05

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは。データアナリティクス事業本部のまつおかです。外出自粛要請の出ている今年のGW、いかがお過ごしでしょうか。爽やかで気持ちの良い季節なのに外出できなくて残念ですが、少しでも楽しく過ごせればと思います。

さて今日は、AlteryxでExcelファイルを読み込んだ際に日付の列が数値として読み込まれた場合の対処法をご紹介します。

Excelの日付が数値として読み込まれる原因

Excelでは日付として扱っていた列が、Alteryxでは数値として読み込まれてしまうという事象は、Excel側の書式の設定が影響しているようです。また書式設定の条件によって、Alteryx側での見た目は数値であってもデータ型は「V_WString」であったり、また「Double」となったりする場合があります。

Excelで使用されている「シリアル値」とは

ではAlteryxで読み込まれた謎の数値は何なのでしょうか。それはExcelで使用されている「シリアル値」と言われるもので、Excel上で日付の計算がしやすいように日付を数値に置き換えたものです。 1900年1月1日を「1」として、そこから連番で管理されています。また、時間も数値で管理されており、24時間を「1」とした小数の値となります。

シリアル値日付のシリアル値時間のシリアル値日付時刻計算エクセル Excel [エクセルの学校]

Alteryxでシリアル値を日付に変換

日付が年月日だけなのか、時間まで含まれているかで変換方法が異なりますので、それぞれ分けてご説明します。

シリアル値が年月日だけの場合

以下のようなデータを用意しました。 A列には標準の日付型のデータを、B列にはA列を参照する形で同じデータが入っていますが、表示形式をユーザ定義で「m月d日」に変更しています。

こちらのデータをAlteryxで読み込むと、以下のようになります。 A列の日付1はデータ型はDateで日付として読み込まれていますが、B列の日付2はデータ型がDoubleで数値となってしまっています。 1行目の「43952」が「2020年5年1日」、2行目の「43953」が「2020年5年2日」・・・を意味しています。

シリアル値は「1」が「1900年1月1日」ですので、本来であれば「1899年12月31日」にシリアル値を加算すれば正しい日付に変換することが可能なのですが、実際は「1899年12月30日」に加算する必要があります。これはExcelの仕様で1900年が閏年の扱いとなっており、実際よりも1日多い想定となっているためです。

少し余談になりますが、みなさんは閏年は4年に1度とは限らないということをご存知でしょうか。
うるう年は4年に1度とは限らない！うるう年の正しい計算方法など8選 – 社会人の教科書

1900年は4や100では割り切れるのですが400では割り切れないので本来であれば閏年でないのですが、Excelでは閏年扱いとなっているため、1900年2月29日が存在することで実際より1日分多くなっています。わかりやすい例で言うとExcelに「1900/2/29」と入力すると日付扱いになりますが、Googleスプレッドシートに同じ値を入力すると日付ではなく文字列となってしまいます。このような理由によりAlteryxでシリアル値を日付に変換する場合は「1899年12月30日」にシリアル値を加算する必要があります。

では実際にフォーミュラツール（Formula Tool）で式を入力してみましょう。日付の加算には「DateTimeAdd」関数を使います。詳しくはこちらをご覧ください。
AlteryxのFormula式における日時操作いろいろ | Developers.IO

「1899年12月30日」にシリアル値を加算するには以下のように入力します。

DateTimeAdd("1899-12-30",[日付2],"days")

これで実行すると、以下のように日付に変換され、日付1と同じ値になりました！

シリアル値に時間も含まれる場合

時間が含まれる場合として以下のようなデータを用意しました。年月日の場合とほぼ同じですが、A列には標準の日時型のデータを、B列にはA列を参照する形で同じデータが入っていますが、表示形式をユーザ定義で「m月d日」に変更しています。

こちらのデータをAlteryxで読み込むと、以下のようになります。

年月日の時ように日時2が数値になっていますが、小数点以下が時間を表す数値となります。日付型の変換時と異なるところは、日付部分と時間部分を分けて加算するところです。

まずはFloor関数を使って小数点以下の時間となる部分の数値を抜き出します。

[日時2]-FLOOR([日時2])

次に抜き出した数値を秒に換算します。24時間で「1」ですので、時間の数値×24(時間)×60(分)×60(秒)で秒数を算出することができます。

[時間]*24*60*60

これで秒数が出せましたので、日付型の時と同様に先に「1899年12月30日」に日数をプラスします。DateTimeAdd関数の2つ目の引数である加算値は、整数部のみ有効となりますので小数以下が入ったままの値を指定しても問題ありません。

DateTimeAdd("1899-12-30",[日時2],"days")

次に秒数をプラスします。

DateTimeAdd([日数プラス],[秒],"seconds")

以上で変換は完了ですが、ここまでの時間の抜き出しや秒の算出を個別にせず、全て同じ式に含めてしまうことも可能です。

DateTimeAdd([日数プラス],([日時2]-FLOOR([日時2]))*24*60*60,"seconds")

これで実行すると、以下のように日時に変換されます。「秒プラス」と「一気に」の列で日時型に変換できていることが確認できます。ただ、ご覧の通り所々1秒のズレが出ている箇所があります。時間のシリアル値は24時間を「1」で表現していることで割り切れない値があり、どうしても1秒程度の誤差が出てきてしまうことがあるのです。

さいごに

AlteryxでExcelを読み込んだ際に日付が数値になっていて「あぁ、また問題勃発・・・」と肩を落としたあなたも、この方法で無事解決です！ただ、時間表示は誤差の出る可能性があることにご注意ください。回避策としてはExcel側の書式をユーザ定義ではなく日時の型で揃えることです。（そうするとこの記事が何の役にも立たなくなりますが！）

では、残りのGWも楽しいAlteryxライフを！